package com.home766.dao.impl;

import com.home766.dao.SchemeProductDao;
import com.home766.dto.SchemeProductDto;
import com.home766.entity.SchemeProduct;
import com.home766.util.StringUtil;
import net.biz.Page;
import net.biz.Pageable;
import net.biz.dao.NativeBaseDao;
import net.biz.dao.impl.BaseDaoImpl;
import net.biz.entity.Brand;
import net.biz.entity.Specification;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import javax.persistence.FlushModeType;
import javax.persistence.NoResultException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *  方案产品-daoImpl
 */
@Repository("schemeProductDaoImpl")
public class SchemeProductDaoImpl extends BaseDaoImpl<SchemeProduct, Long> implements SchemeProductDao {
	
	@Resource(name="nativeBaseDaoImpl")
	private NativeBaseDao nativeBaseDao;
	
	/**
	 * 检查名称是否存在
	 * @param name
	 * @return
	 */
	@Override
	public boolean checkExists(String name) {
		if (StringUtil.isNullOrSpace(name)) {
			return false;
		}
		String hql = "select count(*) from SchemeProduct sp where lower(sp.name) = lower(:name) and sp.isDelete != 1 ";
		Long count = entityManager.createQuery(hql, Long.class).setParameter("name", name).getSingleResult();
		return count > 0;
	}

	/**
	 * 根据方案类型查询对应方案产品数据集合
	 * @param type
	 * @return
	 */
	@Override
	public List<SchemeProduct> findSchemeProductByType(Long type, String sn, String name) {
		String hql = " from SchemeProduct sp where sp.type = :type and sp.isDelete != 1 ";
		if(!StringUtil.isNullOrSpace(sn)){
			hql += " and sp.sn like '%"+sn+"%' ";
		}
		if(!StringUtil.isNullOrSpace(name )){
			hql += " and sp.name like '%"+name+"%' ";
		}
		return entityManager.createQuery(hql,SchemeProduct.class).setParameter("type", type).getResultList();
	}
	
	@Override
	public List<SchemeProduct> findSchemeProductByCategory(Long category, String sn, String name) {
		String hql = " from SchemeProduct sp where sp.categoryId = :category and sp.isDelete != 1 ";
		if(!StringUtil.isNullOrSpace(sn)){
			hql += " and sp.sn like '%"+sn+"%' ";
		}
		if(!StringUtil.isNullOrSpace(name )){
			hql += " and sp.name like '%"+name+"%' ";
		}
		return entityManager.createQuery(hql,SchemeProduct.class).setParameter("category", category).getResultList();
	}
	
	/**
	 * 方案产品分页查询
	 * @param pageable
	 * @param sn            编码
	 * @param name          产品名称
	 * @param createPerson  创建人
	 * @param beginDate     
	 * @param endDate
	 * @param type          类型
	 * @param brand         品牌
	 * @return
	 */
	@Override
	public Page<SchemeProductDto> findPage(Pageable pageable, String sn, String name, String createPerson, Date beginDate, Date endDate, Long type, Long brand) {
		StringBuffer sql = new StringBuffer();
		sql.append("  SELECT                                          "); 
		sql.append("    sp.id,                                        "); 
		sql.append("    sp.create_date,                               "); 
		sql.append("    sp.name,                                      "); 
		sql.append("    sp.sn,                                        "); 
		sql.append("    sp.price,                                     "); 
		sql.append("    sp.type,                                      "); 
		sql.append("    sp.is_tehui,                                  "); 
		sql.append("    sp.create_person,                             "); 
		sql.append("    xb.name        AS brand_name,                 "); 
		sql.append("    xv.name        AS specification_value,        "); 
		sql.append("    dic.title      AS type_name,                  "); 
		sql.append("    dict.title     AS category_name               "); 
		sql.append("  FROM 766_scheme_product sp                      "); 
		sql.append("    LEFT JOIN xx_brand xb                         "); 
		sql.append("      ON xb.id = sp.brand                         "); 
		sql.append("    LEFT JOIN xx_specification_value xv           "); 
		sql.append("      ON xv.id = sp.specification_value           "); 
//		sql.append("    LEFT JOIN xx_admin xad                        "); 
//		sql.append("      ON xad.id = sp.create_person                ");
		sql.append("    LEFT JOIN 766_dictionary dic                  "); 
		sql.append("      ON dic.id = sp.type                         ");
		sql.append("    LEFT JOIN 766_dictionary dict                 "); 
		sql.append("      ON dict.id = sp.category                    ");
		sql.append("  WHERE sp.is_delete != 1                         ");
		
		Map<String,Object> map = new HashMap<String, Object>();
		if((!StringUtil.isNullOrSpace(pageable.getSearchProperty())) && (!StringUtil.isNullOrSpace(pageable.getSearchValue()))){
			String searchName = pageable.getSearchProperty();
			sql.append(" and sp."+searchName+" like :"+searchName+" ");
			map.put(searchName, "%"+pageable.getSearchValue()+"%");
		}
		sql.append(" order by sp.create_date desc ");
		return nativeBaseDao.findPage(sql.toString(), map, pageable, SchemeProductDto.class);
		
	}
	
	/**
	 * 根据编号查询主材产品
	 */
	@Override
	public SchemeProduct findBySn(String sn) {
		if (sn == null) {
			return null;
		}
		String jpql = "select schemeProduct from SchemeProduct schemeProduct where lower(schemeProduct.sn) = lower(:sn) and schemeProduct.isDelete != 1 ";
		try {
			return entityManager.createQuery(jpql, SchemeProduct.class).setFlushMode(FlushModeType.COMMIT).setParameter("sn", sn).getSingleResult();
		} catch (NoResultException e) {
			return null;
		}
	}
	
	/**
	 * 根据分类查询主材产品集合
	 * @param categoryId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<SchemeProduct> findByCategory(Long categoryId, Boolean bool) {
		if(bool==null){
			bool=false;
		}
		String sql = " select sp.* from  766_scheme_product sp where sp.name_id = :categoryId and sp.is_delete != 1 ";
		if(bool){
			sql+=" and (sp.is_tehui=0 or sp.is_tehui is null)";
		}
		return entityManager.createNativeQuery(sql, SchemeProduct.class).setParameter("categoryId", categoryId).getResultList();
	}
	
	/**
	 * 验证规格是否选为方案主材规格
	 * @param specification
	 * @return
	 */
	@Override
	public boolean checkInSchemeProduct(Specification specification) {
		if(specification == null){
			return false;
		}
		String sql = " SELECT count(*) FROM SchemeProduct sp WHERE sp.specification = :specification and sp.isDelete != 1 ";
		Long count = entityManager.createQuery(sql,Long.class).setParameter("specification", specification).getSingleResult();
		return count > 0;
	}

	@Override
	public boolean checkBrandInSchemeProduct(Brand brand) {
		if(brand == null){
			return false;			
		}
		String sql = " SELECT count(*) FROM SchemeProduct sp WHERE sp.brand = :brand and sp.isDelete != 1 ";
		Long count = entityManager.createQuery(sql,Long.class).setParameter("brand", brand).getSingleResult();
		return count > 0;
	}

	@Override
	public List<SchemeProductDto> getSchemeProductDtoList() {
		// TODO Auto-generated method stub
		String sql = "select distinct br.`name` as name,br.id from xx_brand br"
				+ " inner join 766_scheme_product sp on sp.brand=br.id"
				+ " inner join 766_dictionary dic on dic.id=sp.type"
				+ " where sp.name ='壁挂炉' and dic.id=14";
		return nativeBaseDao.findList(sql, null, SchemeProductDto.class);
	}

	@Override
	public List<SchemeProductDto> getSchemeProductDtoListOfAuxiliary() {
		// TODO Auto-generated method stub
		String sql = "select distinct br.`name` as name,br.id from xx_brand br"
				+ " inner join 766_scheme_product sp on sp.brand=br.id"
				+ " inner join 766_dictionary dic on dic.id=sp.type"
				+ " where sp.name <>'壁挂炉' and dic.id=14";
		return nativeBaseDao.findList(sql, null, SchemeProductDto.class);
	}

	@Override
	public List<SchemeProductDto> getSchemeProductDtoListByConditioner() {
		// TODO Auto-generated method stub
		String sql ="select distinct br.`name`,br.id from xx_brand br "
				+ " inner join 766_scheme_product sp on sp.brand=br.id"
				+ " inner join 766_dictionary dic on dic.id=sp.type"
				+ " where sp.name <>'提升泵' and sp.name<>'控制器' and dic.id=105";
		return nativeBaseDao.findList(sql, null, SchemeProductDto.class);
	}
	
}
